package com.clownfish7.flink.tableapi.sql.queries;

/**
 * classname WindowFunction
 * description 窗口函数
 * create 2022-01-10 13:07
 */
public class WindowFunction {

    /**
     * -- tables must have time attribute, e.g. `bidtime` in this table
     * Flink SQL> desc Bid;
     * +-------------+------------------------+------+-----+--------+---------------------------------+
     * |        name |                   type | null | key | extras |                       watermark |
     * +-------------+------------------------+------+-----+--------+---------------------------------+
     * |     bidtime | TIMESTAMP(3) *ROWTIME* | true |     |        | `bidtime` - INTERVAL '1' SECOND |
     * |       price |         DECIMAL(10, 2) | true |     |        |                                 |
     * |        item |                 STRING | true |     |        |                                 |
     * +-------------+------------------------+------+-----+--------+---------------------------------+
     * <p>
     * Flink SQL> SELECT * FROM Bid;
     * +------------------+-------+------+
     * |          bidtime | price | item |
     * +------------------+-------+------+
     * | 2020-04-15 08:05 |  4.00 | C    |
     * | 2020-04-15 08:07 |  2.00 | A    |
     * | 2020-04-15 08:09 |  5.00 | D    |
     * | 2020-04-15 08:11 |  3.00 | B    |
     * | 2020-04-15 08:13 |  1.00 | E    |
     * | 2020-04-15 08:17 |  6.00 | F    |
     * +------------------+-------+------+
     */


//    ------------------------------------------------------------------------------------------------------------------
//    ------------------------------------------------------------------------------------------------------------------
//    ------------------------------------------------------------------------------------------------------------------

    /**
     * 滚动窗口
     * TUMBLE(TABLE data, DESCRIPTOR(timecol), size [, offset ])
     * data     ：是一个表参数，可以是与时间属性列的任何关系。
     * timecol  ：是一个列描述符，指示应将数据的哪个时间属性列映射到翻滚窗口。
     * size     ：是指定翻滚窗口宽度的持续时间。
     * offset   ：是一个可选参数，用于指定窗口启动将移动的偏移量。
     */

    String tumbleSql1 = "SELECT * FROM TABLE( " +
            "         TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES)" +
            "     );";

    // +------------------+-------+------+------------------+------------------+-------------------------+
    // |          bidtime | price | item |     window_start |       window_end |            window_time  |
    // +------------------+-------+------+------------------+------------------+-------------------------+
    // | 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
    // | 2020-04-15 08:07 |  2.00 | A    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
    // | 2020-04-15 08:09 |  5.00 | D    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
    // | 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
    // | 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
    // | 2020-04-15 08:17 |  6.00 | F    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
    // +------------------+-------+------+------------------+------------------+-------------------------+


    String tumbleSql2 = "SELECT window_start, window_end, SUM(price) FROM TABLE( " +
            "                TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES)" +
            "            ) GROUP BY window_start, window_end;";
    // +------------------+------------------+-------+
    // |     window_start |       window_end | price |
    // +------------------+------------------+-------+
    // | 2020-04-15 08:00 | 2020-04-15 08:10 | 11.00 |
    // | 2020-04-15 08:10 | 2020-04-15 08:20 | 10.00 |
    // +------------------+------------------+-------+


//    ------------------------------------------------------------------------------------------------------------------
//    ------------------------------------------------------------------------------------------------------------------
//    ------------------------------------------------------------------------------------------------------------------

    /**
     * 滑动窗口
     * HOP(TABLE data, DESCRIPTOR(timecol), slide, size [, offset ])
     * data     ：是一个表参数，可以是与时间属性列的任何关系。
     * timecol  ：是一个列描述符，指示应将数据的哪些时间属性列映射到跳跃窗口。
     * slide    ：是指定顺序跳转窗口开始之间的持续时间的持续时间
     * size     ：是指定跳转窗口宽度的持续时间。
     * offset   ：是一个可选参数，用于指定窗口启动将移动的偏移量。
     */

    String hopSql1 = "SELECT * FROM TABLE( " +
            "          HOP(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '5' MINUTES, INTERVAL '10' MINUTES)" +
            "      );";

    // +------------------+-------+------+------------------+------------------+-------------------------+
    // |          bidtime | price | item |     window_start |       window_end |           window_time   |
    // +------------------+-------+------+------------------+------------------+-------------------------+
    // | 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
    // | 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:05 | 2020-04-15 08:15 | 2020-04-15 08:14:59.999 |
    // | 2020-04-15 08:07 |  2.00 | A    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
    // | 2020-04-15 08:07 |  2.00 | A    | 2020-04-15 08:05 | 2020-04-15 08:15 | 2020-04-15 08:14:59.999 |
    // | 2020-04-15 08:09 |  5.00 | D    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
    // | 2020-04-15 08:09 |  5.00 | D    | 2020-04-15 08:05 | 2020-04-15 08:15 | 2020-04-15 08:14:59.999 |
    // | 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:05 | 2020-04-15 08:15 | 2020-04-15 08:14:59.999 |
    // | 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
    // | 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:05 | 2020-04-15 08:15 | 2020-04-15 08:14:59.999 |
    // | 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
    // | 2020-04-15 08:17 |  6.00 | F    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
    // | 2020-04-15 08:17 |  6.00 | F    | 2020-04-15 08:15 | 2020-04-15 08:25 | 2020-04-15 08:24:59.999 |
    // +------------------+-------+------+------------------+------------------+-------------------------+

    String hopSql2 = "SELECT window_start, window_end, SUM(price) FROM TABLE( " +
            "          HOP(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '5' MINUTES, INTERVAL '10' MINUTES)" +
            "      ) GROUP BY window_start, window_end;";

    // +------------------+------------------+-------+
    // |     window_start |       window_end | price |
    // +------------------+------------------+-------+
    // | 2020-04-15 08:00 | 2020-04-15 08:10 | 11.00 |
    // | 2020-04-15 08:05 | 2020-04-15 08:15 | 15.00 |
    // | 2020-04-15 08:10 | 2020-04-15 08:20 | 10.00 |
    // | 2020-04-15 08:15 | 2020-04-15 08:25 |  6.00 |
    // +------------------+------------------+-------+


//    ------------------------------------------------------------------------------------------------------------------
//    ------------------------------------------------------------------------------------------------------------------
//    ------------------------------------------------------------------------------------------------------------------

    /**
     * 累计窗口
     * CUMULATE(TABLE data, DESCRIPTOR(timecol), step, size)
     * data     ：是一个表参数，可以是与时间属性列的任何关系。
     * timecol  ：是一个列描述符，指示应将数据的哪个时间属性列映射到翻滚窗口。
     * step     ：是指定在顺序累积窗口结束之间增加的窗口大小的持续时间。
     * size     ：是指定累积窗口的最大宽度的持续时间。 必须是 的整数倍。sizestep
     * offset   ：是一个可选参数，用于指定窗口启动将移动的偏移量
     */

    String cumulateSql1 = "SELECT * FROM TABLE( " +
            "                  CUMULATE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '2' MINUTES, INTERVAL '10' MINUTES)" +
            "              );";

    // +------------------+-------+------+------------------+------------------+-------------------------+
    // |          bidtime | price | item |     window_start |       window_end |            window_time  |
    // +------------------+-------+------+------------------+------------------+-------------------------+
    // | 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:00 | 2020-04-15 08:06 | 2020-04-15 08:05:59.999 |
    // | 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:00 | 2020-04-15 08:08 | 2020-04-15 08:07:59.999 |
    // | 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
    // | 2020-04-15 08:07 |  2.00 | A    | 2020-04-15 08:00 | 2020-04-15 08:08 | 2020-04-15 08:07:59.999 |
    // | 2020-04-15 08:07 |  2.00 | A    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
    // | 2020-04-15 08:09 |  5.00 | D    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
    // | 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:12 | 2020-04-15 08:11:59.999 |
    // | 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:14 | 2020-04-15 08:13:59.999 |
    // | 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:16 | 2020-04-15 08:15:59.999 |
    // | 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:18 | 2020-04-15 08:17:59.999 |
    // | 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
    // | 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:10 | 2020-04-15 08:14 | 2020-04-15 08:13:59.999 |
    // | 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:10 | 2020-04-15 08:16 | 2020-04-15 08:15:59.999 |
    // | 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:10 | 2020-04-15 08:18 | 2020-04-15 08:17:59.999 |
    // | 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
    // | 2020-04-15 08:17 |  6.00 | F    | 2020-04-15 08:10 | 2020-04-15 08:18 | 2020-04-15 08:17:59.999 |
    // | 2020-04-15 08:17 |  6.00 | F    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
    // +------------------+-------+------+------------------+------------------+-------------------------+

    String cumulateSql2 = "SELECT window_start, window_end, SUM(price) FROM TABLE( " +
            "                  CUMULATE(TABLE Biz, DESCRIPTOR(bidtime), INTERVAL '2' MINUTES, INTERVAL '10' MINUTES)" +
            "              ) GROUP BY window_start, window_end;";

    // +------------------+------------------+-------+
    // |     window_start |       window_end | price |
    // +------------------+------------------+-------+
    // | 2020-04-15 08:00 | 2020-04-15 08:06 |  4.00 |
    // | 2020-04-15 08:00 | 2020-04-15 08:08 |  6.00 |
    // | 2020-04-15 08:00 | 2020-04-15 08:10 | 11.00 |
    // | 2020-04-15 08:10 | 2020-04-15 08:12 |  3.00 |
    // | 2020-04-15 08:10 | 2020-04-15 08:14 |  4.00 |
    // | 2020-04-15 08:10 | 2020-04-15 08:16 |  4.00 |
    // | 2020-04-15 08:10 | 2020-04-15 08:18 | 10.00 |
    // | 2020-04-15 08:10 | 2020-04-15 08:20 | 10.00 |
    // +------------------+------------------+-------+


//    ------------------------------------------------------------------------------------------------------------------
//    ------------------------------------------------------------------------------------------------------------------
//    ------------------------------------------------------------------------------------------------------------------

    /**
     * 偏移量
     * offset-16 MINUTE     2021-06-29 23:54:002021-06-30 00:04:00
     * offset-6  MINUTE     2021-06-29 23:54:002021-06-30 00:04:00
     * offset-4  MINUTE     2021-06-29 23:56:002021-06-30 00:06:00
     * offset0              2021-06-30 00:00:002021-06-30 00:10:00
     * offset4   MINUTE     2021-06-29 23:54:002021-06-30 00:04:00
     * offset6   MINUTE     2021-06-29 23:56:002021-06-30 00:06:00
     */

}